package com.dongway.core.store;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.dongway.core.annotation.AutoGeneratedKey;
import com.dongway.core.common.Paginator;
import com.dongway.core.utils.JdbcUtils;
import com.dongway.netcondition.model.DevInfo;

@Repository
public class JdbcDAO {

    public static final String DB_MYSQL = "mysql";
    public static final String DB_MSSQL = "mssql";
    @Autowired
    private JdbcTemplate template;
    private static final Logger logger = LoggerFactory.getLogger(JdbcDAO.class);
    private static JdbcDAO instance = null;
    private static final String PK = "id";


    public static JdbcDAO instance() {
        if (null == instance) {
            instance = new JdbcDAO();
        }
        return instance;
    }


    public JdbcDAO() {
        if (logger.isDebugEnabled()) {
            logger.info("init JdbcDAO````````");
        }
        template = new JdbcTemplate(JdbcUtils.getDataSource("database"));
        instance = this;
    }


    public JdbcTemplate getJdbcTemplate() {
        return template;
    }


    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(getJdbcTemplate());
    }


    public int save(Object entity) {
        Class<?> clazz = entity.getClass();
        SimpleJdbcInsert insert = JdbcUtils.creatSimpleJdbcInsert(clazz, getJdbcTemplate());
        return insert.execute(new BeanPropertySqlParameterSource(entity));
    }


    public int saveByGeneratedId(String tableName, Object entity) {
        return save(tableName, entity, PK);
    }


    public int save(String tableName, Object entity, String... generatedKeys) {
        SimpleJdbcInsert insert =
                new SimpleJdbcInsert(this.getJdbcTemplate()).withTableName(tableName);
        insert.setGeneratedKeyNames(generatedKeys);
        return insert.execute(new BeanPropertySqlParameterSource(entity));
    }


    /**
     * 更新并且忽略对象中的值为null的字段
     * 
     * @param entity
     * @return
     */
    public <T> int updateIgnoreNull(T entity) {
        String sql =
                JdbcUtils.buildUpdateSql(JdbcUtils.getTableName(entity.getClass()), entity, true);
        return this.getNamedParameterJdbcTemplate().update(sql,
            new BeanPropertySqlParameterSource(entity));
    }


    /**
     * 更新，不忽略对象中的空值
     * 
     * @param entity
     * @return
     */
    public <T> int update(T entity) {
        String sql =
                JdbcUtils.buildUpdateSql(JdbcUtils.getTableName(entity.getClass()), entity, false);
        return this.getNamedParameterJdbcTemplate().update(sql,
            new BeanPropertySqlParameterSource(entity));
    }


    public int update(String sql, Object... paraValue) {
        return this.getJdbcTemplate().update(sql, paraValue);
    }


    public int update(String sql, Map<String, Object> paramMap) {
        return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
    }


    public <T> T queryById(Class<T> elementType, Object id) {
        String sql = "SELECT * FROM " + JdbcUtils.getTableName(elementType) + " WHERE id=?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<T>(elementType), id);
    }


    public <T> T queryForObject(Class<T> elementType, String sql, Object... params) {
        if (BeanUtils.isSimpleProperty(elementType)) {
            return getJdbcTemplate().queryForObject(sql, params, elementType);
        } else {
            // catch emptyResultDataAccessException 不存在相应数据返回空
            try {
                return getJdbcTemplate().queryForObject(sql,
                    new BeanPropertyRowMapper<T>(elementType), params);
            } catch (EmptyResultDataAccessException e) {
                return null;
            }
        }
    }


    public <T> T queryForObject(Class<T> elementType, String sql, Map<String, Object> params) {
        if (BeanUtils.isSimpleProperty(elementType)) {
            return getNamedParameterJdbcTemplate().queryForObject(sql, params, elementType);
        } else {
            // catch emptyResultDataAccessException 不存在相应数据返回空
            try {
                return getJdbcTemplate().queryForObject(sql,
                    new BeanPropertyRowMapper<T>(elementType), params);
            } catch (EmptyResultDataAccessException e) {
                return null;
            }
        }
    }


    public <T> List<T> query(Class<T> elementType, String sql, Object... params) {
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<T>(elementType), params);
    }


    public <T> List<T> query(Class<T> elementType, String sql, Map<String, Object> map) {
        return getNamedParameterJdbcTemplate().query(sql, map,
            new BeanPropertyRowMapper<T>(elementType));
    }


    public <T> List<T> query(Class<T> cls, String sql, Map<String, Object> params,
            Paginator paginator) {
        if (null != paginator && 0 != paginator.getPageSize()
                && Integer.MAX_VALUE != paginator.getPageSize()) {
            int fromIdx = sql.toLowerCase().indexOf("from");
            String countSql = "select count(*) " + sql.substring(fromIdx);
            Integer totalRecords = queryForObject(Integer.class, countSql, params);
            paginator.setTotalRecords(totalRecords);
            StringBuilder sb = new StringBuilder(sql);
            if (StringUtils.isNotBlank(paginator.getSort())) {
                sb.append(" order by ");
                sb.append(StringUtils.trimToEmpty(paginator.getAlias()));
                sb.append(StringUtils.trimToEmpty(paginator.getSort()));
                sb.append(" ").append(StringUtils.trimToEmpty(paginator.getDir()));
            }
            sb.append(" limit ");
            sb.append(paginator.getStartIndex()).append(",").append(paginator.getPageSize());
            sql = sb.toString();
        }
        return query(cls, sql, params);
    }


    public <T> List<T> query(Class<T> cls, String sql, Paginator paginator, Object... params) {
        if (null != paginator && 0 != paginator.getPageSize()
                && Integer.MAX_VALUE != paginator.getPageSize()) {
            int fromIdx = sql.toLowerCase().indexOf("from");
            String countSql = "select count(*) " + sql.substring(fromIdx);
            Integer totalRecords = queryForObject(Integer.class, countSql, params);
            paginator.setTotalRecords(totalRecords);
            StringBuilder sb = new StringBuilder(sql);
            if (StringUtils.isNotBlank(paginator.getSort())) {
                sb.append(" order by ");
                sb.append(StringUtils.trimToEmpty(paginator.getAlias()));
                sb.append(StringUtils.trimToEmpty(paginator.getSort()));
                sb.append(" ").append(StringUtils.trimToEmpty(paginator.getDir()));
            }
            sb.append(" limit ");
            sb.append(paginator.getStartIndex()).append(",").append(paginator.getPageSize());
            sql = sb.toString();
        }
        return query(cls, sql, params);
    }


    public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) {
        return getJdbcTemplate().query(sql, rowMapper, args);
    }
    
    public int[] batchUpdate(String sql, List<Object[]> batchArgs) {
        return getJdbcTemplate().batchUpdate(sql, batchArgs);
    }

    
    public int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) {
        return getJdbcTemplate().batchUpdate(sql, pss);
    }

    public int countById(String tableName, Integer id) {
        String sql = "SELECT count(*) FROM" + tableName + " WHERE id=" + id;
        return getJdbcTemplate().queryForObject(sql, Integer.class);
    }


    /**
     * only use for table which has the primary key "id"
     */
    public boolean isExisted(Class<?> clazz, Integer id) {
        String tableName = JdbcUtils.getTableName(clazz);
        String sql = "SELECT count(*) FROM " + tableName + " WHERE id=" + id;
        return getJdbcTemplate().queryForObject(sql, Integer.class) > 1 ? false : true;
    }


    public int deleteById(String tableName, Object id) {
        String sql = "DELETE FROM " + tableName + " WHERE id=?";
        return getJdbcTemplate().update(sql, id);
    }


    public int clearAll(String tableName) {
        String sql = "DELETE FROM" + tableName;
        return getJdbcTemplate().update(sql);
    }


    public void execute(String sql) {
        getJdbcTemplate().execute(sql);
    }


    public static void main(String[] args) throws NoSuchFieldException, SecurityException {

        DevInfo devInfo = new DevInfo();
        devInfo.setId(2);
        // Field[] fields = devInfo.getClass().getDeclaredFields();
        Field field = devInfo.getClass().getDeclaredField("id");
        System.out.println(field.isAnnotationPresent(AutoGeneratedKey.class));
        // String sql = "update t_dev_info set name=2";
        Method[] Methods = devInfo.getClass().getDeclaredMethods();
        for (Method Method : Methods) {
            System.out.println(Method.getName());
        }
        System.out.println(new Timestamp(System.currentTimeMillis()));
        // JdbcDAO.instance().update(devInfo);
        // JdbcDAO.instance().saveGeneratedId("t_dev_info", devInfo);
    }

}
